/**
 * Project Name:kany-utils
 * File Name:ReadExcel.java
 * Package Name:me.kany.utils.excel.poi
 * Date:2016年5月27日下午4:42:41
 * Copyright (c) 2016, Jason.Wang All Rights Reserved.
 *
*/

package me.kany.utils.excel.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * ClassName:ReadExcel <br/>
 * Function: 读取Excel文件. <br/>
 * Date: 2016年5月27日 下午4:42:41 <br/>
 * 
 * @author Jason.Wang
 * @version
 * @since JDK 1.7
 * @see
 */
public class ReadExcel {

	private SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

	/**
	 * 
	 * readExcel:读取Excel测试，兼容 Excel 2003/2007+. <br/>
	 *
	 * @author Jason.Wang
	 * @param excelFile
	 * @return
	 * @since JDK 1.7
	 */
	public List<Map<String, String>> readExcel(File excelFile) {
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		try {
			// 同时支持Excel 2003、2007
			FileInputStream is = new FileInputStream(excelFile); // 文件流
			Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel 2003/2007/2010/2013/2016 都是可以处理的
			int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
			// 遍历每个Sheet
			for (int s = 0; s < sheetCount; s++) {
				Sheet sheet = workbook.getSheetAt(s);
				int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
				Map<String, String> map = new HashMap<String, String>();
				// 遍历每一行
				for (int r = 0; r < rowCount; r++) {
					Row row = sheet.getRow(r);
					int cellCount = row.getPhysicalNumberOfCells(); // 获取总列数
					// 遍历每一列
					for (int c = 0; c < cellCount; c++) {
						Cell cell = row.getCell(c);
						int cellType = cell.getCellType();
						String cellValue = null;
						switch (cellType) {
						case Cell.CELL_TYPE_STRING: // 文本
							cellValue = cell.getStringCellValue();
							break;
						case Cell.CELL_TYPE_NUMERIC: // 数字、日期
							if (DateUtil.isCellDateFormatted(cell)) {
								cellValue = simpleDateFormat.format(cell.getDateCellValue()); // 日期型
							} else {
								cellValue = String.valueOf(cell.getNumericCellValue()); // 数字
							}
							break;
						case Cell.CELL_TYPE_BOOLEAN: // 布尔型
							cellValue = String.valueOf(cell.getBooleanCellValue());
							break;
						case Cell.CELL_TYPE_BLANK: // 空白
							cellValue = cell.getStringCellValue();
							break;
						case Cell.CELL_TYPE_ERROR: // 错误
							cellValue = "错误";
							break;
						case Cell.CELL_TYPE_FORMULA: // 公式
							cellValue = "错误";
							break;
						default:
							cellValue = "错误";
						}
						map.put("C" + (c + 1), cellValue);
					}
					list.add(map);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 
	 * readExcel:读取Excel测试，兼容 Excel 2003/2007+. <br/>
	 *
	 * @author Jason.Wang
	 * @param excelFile
	 * @return
	 * @since JDK 1.7
	 */
	public List<Map<String, String>> readExcel(InputStream inputStream) {
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		try {
			// 同时支持Excel 2003、2007
			Workbook workbook = WorkbookFactory.create(inputStream); // 这种方式 Excel 2003/2007/2010/2013/2016 都是可以处理的
			int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
			// 遍历每个Sheet
			for (int s = 0; s < sheetCount; s++) {
				Sheet sheet = workbook.getSheetAt(s);
				int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
				// 遍历每一行
				for (int r = 0; r < rowCount; r++) {
					Row row = sheet.getRow(r);
					Map<String, String> map = new HashMap<String, String>();
					int cellCount = row.getPhysicalNumberOfCells(); // 获取总列数
					// 遍历每一列
					for (int c = 0; c < cellCount; c++) {
						Cell cell = row.getCell(c);
						String cellValue = null;
						try {
							int cellType = cell.getCellType();
							switch (cellType) {
							case Cell.CELL_TYPE_STRING: // 文本
								cellValue = cell.getStringCellValue();
								break;
							case Cell.CELL_TYPE_NUMERIC: // 数字、日期
								if (DateUtil.isCellDateFormatted(cell)) {
									cellValue = simpleDateFormat.format(cell.getDateCellValue()); // 日期型
								} else {
									cellValue = String.valueOf(cell.getNumericCellValue()); // 数字
								}
								break;
							case Cell.CELL_TYPE_BOOLEAN: // 布尔型
								cellValue = String.valueOf(cell.getBooleanCellValue());
								break;
							case Cell.CELL_TYPE_BLANK: // 空白
								cellValue = cell.getStringCellValue();
								break;
							case Cell.CELL_TYPE_ERROR: // 错误
								cellValue = "错误";
								break;
							case Cell.CELL_TYPE_FORMULA: // 公式
								cellValue = "错误";
								break;
							default:
								cellValue = "错误";
							}
						} catch (Exception e) {
							cellValue = "错误";
						}
						map.put("C" + (c + 1), cellValue);
					}
					list.add(map);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
}
